from datetime import datetime
from functools import lru_cache

from sqlalchemy import Column
from sqlalchemy import String
from sqlalchemy import Integer
from sqlalchemy import DateTime
from sqlalchemy import func
from sqlalchemy import DECIMAL
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.exc import ResourceClosedError

from injection_service.restful.exception import SQLError

Base = declarative_base()


class User(Base):
    __tablename__ = "user"

    id = Column(Integer, primary_key=True)
    username = Column(String(20), doc='用户名')
    password = Column(String(128), doc='用户密码')
    created = Column(DateTime, server_default=func.now())
    __mapper_args__ = {"eager_defaults": True}


class Order(Base):
    __tablename__ = "order"
    id = Column(Integer, primary_key=True)
    name = Column(String(20), doc='订单名称')
    count = Column(Integer, default=0, doc='订单数量')
    price = Column(DECIMAL, doc='价格', default=0.00)
    user_id = Column(Integer, doc='用户ID')


@lru_cache()
def get_engine_session(database_url: str):
    """获取engine 对象 异步session类"""
    engine = create_async_engine(
        database_url,
        echo=True,
    )

    async_session = sessionmaker(
        engine, expire_on_commit=True, class_=AsyncSession
    )
    return engine, async_session


async def manually(request, sql: str):
    """执行sql"""
    try:
        results = await request.app.state.db.execute(sql)
        await request.app.state.db.commit()
        data = [dict(zip(result.keys(), result)) for result in results]
        for value in data:
            for k, v in value.items():
                if isinstance(v, datetime):
                    value[k] = str(v)
        return data
    except ResourceClosedError:
        return None
    except Exception as e:
        raise SQLError(e)


__all__ = ["Base", "get_engine_session", "manually"]
